<?php
/**
 * Created by PhpStorm.
 * User: kwibon
 * Date: 2018/12/17
 * Time: 17:51
 */

namespace app\api\controller;


class ExportExcel
{
    public function index ()
    {
        $str = "★★★★★";
        $str = str_replace("★","a",$str);
        $str = strlen($str);

        echo "<pre>";
        var_dump($str);
        echo "</pre>";

        exit;


        echo "导出Excel Class";
    }


    /**
     * 导出excel表格（适合没有单元格合并的情况）
     * @param array $data 二维数组
     * @param array $table_head 表头（即excel工作表的第一行标题）
     * @param string $file_name 文件名
     * @param string $sheet_name 工作表名
     */
    public function exportExcel($data=array(), $table_head=array(), $file_name='excel', $sheet_name='sheet')
    {
        vendor('PHPExcel.PHPExcel');
        $objPHPExcel = new \PHPExcel();  // 创建PHPExcel对象

        // 设置excel文件的属性，在excel文件->属性->详细信息，可以看到这些值
        $objPHPExcel->getProperties()  //获得文件属性对象，给下文提供设置资源
        ->setTitle( "Office 2007 XLSX Record Document" );    //标题

        // 设置Excel文档的第一张sheet（工作表）为活动表，即当前操作的表。
        $objPHPExcel->setActiveSheetIndex(0);

        // 获取当前操作的工作表
        $activeSheet = $objPHPExcel->getActiveSheet();

        // 设置工作表的名称
        $activeSheet->setTitle($sheet_name);

        // 返回字符A的  ASCII 码值
        $column = ord('A');

        // 设置工作表的表头
        foreach ($table_head as $k=>$v) {
            // 字体大小
            $activeSheet->getStyle(chr($column)."1")->getFont()->setSize(13);
            // 加粗
            $activeSheet->getStyle(chr($column)."1")->getFont()->setBold(true);
            // 列宽
            $chars = strlen($v);   // 统计字节数
            $activeSheet->getColumnDimension(chr($column))->setWidth($chars*2);
            // 设置单元格的值
            $activeSheet->setCellValue(chr($column)."1", $v);
            // 设置左对齐
            $activeSheet->getStyle(chr($column))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

            $column++;
        }

        $column = ord('A');   // 返回字符的  ASCII 码值
        // 将$data中的数据填充到单元格中
        foreach ($data as $row=>$col) {
            $i=0;
            foreach ($col as $k=>$v ) {
                $activeSheet->setCellValue(chr($column+$i).($row+2), $v);
                $i++;
            }
        }

        // 导出Excel表格
        $file_name .= date('Ymd');   // 文件名
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$file_name.'.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter->save('php://output');
    }

    //调用示例
    public function export_order()
    {
        $data = array(
            array('id'=>1, 'name'=>'小三', 'id_card'=>'420928199003065555'),
            array('id'=>2, 'name'=>'小四', 'id_card'=>'420928199003066666'),
            array('id'=>3, 'name'=>'小五', 'id_card'=>'420928199003067777'),
            array('id'=>4, 'name'=>'小六', 'id_card'=>'420928199003068888'),
        );
        $table_head = array('用户ID', '姓名', '身份证号');
        $this->exportExcel($data, $table_head, "测试");
    }

    //过滤emoji
    public function filterEmoji($emojiStr){
        $emojiStr = preg_replace_callback('/./u',function(array $match){
            return strlen($match[0]) >= 4 ? '' : $match[0];
        },$emojiStr);
        return $emojiStr;
    }

    //读取Excel
    public function readExcel(){
        Vendor('PHPExcel.PHPExcel');//包的存放是\ThinkPHP\Extend\Vendor\PHPExcel

        $file = '/var/www/html/php_test_station/1111.xlsx';
        //判断文件是什么格式
        $type = pathinfo($file);
        $type = strtolower($type["extension"]);
        $type = $type==='csv' ? $type : 'Excel2007';//这里需要与格式对应
        ini_set('max_execution_time', '0');

        //判断使用哪种格式
        $objReader = \PHPExcel_IOFactory::createReader($type);
        $objPHPExcel = $objReader->load($file);
        $sheetCount = $objPHPExcel->getSheetCount();
        $sheetNameArr = $objPHPExcel->getSheetNames();

        $hotelData = [];
        for($i=0;$i<$sheetCount;$i++) {
            $sheet = $objPHPExcel->getSheet($i);
            $sheetName = $sheetNameArr[$i];
            $allColumn = $sheet->getHighestColumn();//**取得最大的列号*/
            $allRow = $sheet->getHighestRow();//**取得一共有多少行*/
            $ColumnNum = \PHPExcel_Cell::columnIndexFromString($allColumn);// 列号 转 列数

            $data = [];
            for($rowIndex=2;$rowIndex<=$allRow;$rowIndex++){
                //循环读取每个单元格的内容。注意行从1开始，列从A开始
                for($colIndex=0;$colIndex<=$ColumnNum;$colIndex++){
                    $data[$rowIndex][] =(string)$sheet->getCellByColumnAndRow($colIndex, $rowIndex)->getValue();
                }
            }

            $hotelData[] = [
                'name'=>$sheetName,
                'data'=>$data
            ];
        }

        echo "<pre>";
        var_dump($hotelData);
        echo "</pre>";

        exit;
    }

    //读取美景酒店Excel数据，并执行生成POI和hotel数据插入
    public function readHotelExcel(){
        Vendor('PHPExcel.PHPExcel');//包的存放是\ThinkPHP\Extend\Vendor\PHPExcel

        $file = '/var/www/html/php_test_station/1111.xlsx';
        //判断文件是什么格式
        $type = pathinfo($file);
        $type = strtolower($type["extension"]);
        $type = $type==='csv' ? $type : 'Excel2007';//这里需要与格式对应
        ini_set('max_execution_time', '0');

        //判断使用哪种格式
        $objReader = \PHPExcel_IOFactory::createReader($type);
        $objPHPExcel = $objReader->load($file);
        $sheetCount = $objPHPExcel->getSheetCount();
        $sheetNameArr = $objPHPExcel->getSheetNames();

        if($sheetCount<1){
            echo "sheet < 1";
            exit;
        }

        $hotelData = [];
        for($i=0;$i<$sheetCount;$i++) {
            $sheet = $objPHPExcel->getSheet($i);
            $sheetName = $sheetNameArr[$i];
            $allRow = $sheet->getHighestRow();//**取得一共有多少行*/

            $remarkText = '';
            if($i==0 || $i==1){
                $remarkText = "    <br><br>所有价格含税，所有餐含税含小费，可安排酒店外早$20/人";
            }

            $data = [];
            $lastIndex = '';
            for($rowIndex=2;$rowIndex<=$allRow;$rowIndex++){
                $hotelName = (string)$sheet->getCellByColumnAndRow(0, $rowIndex)->getValue();
                $priceDay = (string)$sheet->getCellByColumnAndRow(2, $rowIndex)->getValue();
                if($priceDay==''){//空行跳过
                    $lastIndex = '';
                    continue;
                }else{
                    if($hotelName==''){
                        $price = (string)$sheet->getCellByColumnAndRow(3, $rowIndex)->getValue();
                        if(strstr($price,'=') !== false){
                            $price = str_replace("=","",$price);
                            $price = eval("return $price;");
                            $price = round($price);
                        }
                        $data[$lastIndex]['priceDay'][] = [
                            'day'=>(string)$sheet->getCellByColumnAndRow(2, $rowIndex)->getValue(),
                            'price'=>$price,
                        ];
                        continue;
                    }else{
                        $str = (string)$sheet->getCellByColumnAndRow(0, $rowIndex)->getValue();
                        $arr = explode("\n",$str);

                        $data[$rowIndex]['name'] = $arr[0];
                        $data[$rowIndex]['address'] = !empty($arr[2]) ? $arr[2] : '';
                        $data[$rowIndex]['rank'] = (string)$sheet->getCellByColumnAndRow(1, $rowIndex)->getValue();
                        $price = (string)$sheet->getCellByColumnAndRow(3, $rowIndex)->getValue();
                        if(strstr($price,'=') !== false){
                            $price = str_replace("=","",$price);
                            $price = eval("return $price;");
                            $price = round($price);
                        }
                        $data[$rowIndex]['priceDay'][] = [
                            'day'=>(string)$sheet->getCellByColumnAndRow(2, $rowIndex)->getValue(),
                            'price'=>$price,
                        ];
                        $data[$rowIndex]['breakfast_fee'] = (string)$sheet->getCellByColumnAndRow(4, $rowIndex)->getValue();
                        $data[$rowIndex]['charging_fee'] = (string)$sheet->getCellByColumnAndRow(5, $rowIndex)->getValue();
                        $data[$rowIndex]['service_fee'] = (string)$sheet->getCellByColumnAndRow(6, $rowIndex)->getValue();
                        $remarks = (string)$sheet->getCellByColumnAndRow(7, $rowIndex)->getValue();
                        $data[$rowIndex]['remarks'] = $remarks.$remarkText;

                        if(is_numeric($data[$rowIndex]['charging_fee'])){
                            $data[$rowIndex]['charging_fee'] = "$".$data[$rowIndex]['charging_fee'];
                        }

                        $lastIndex = $rowIndex;
                    }
                }
            }

            $hotelData[] = [
                'name'=>$sheetName,
                'data'=>$data
            ];
        }

        //保存数据
        $this->saveHotel($hotelData);


//        echo "<pre>";
//        var_dump($hotelData);
//        echo "</pre>";

        exit;
    }

    public function saveHotel($hotelData){
        if(empty($hotelData)) return false;
        echo "**********开始插入基础数据*********<br>";
        foreach ($hotelData as $data) {
            $cityName = trim($data['name']);
            foreach ($data['data'] as $key=>$value) {
                $rank = str_replace("★","a",$value['rank']);
                $rank = strlen($rank);
                $priceData = $value['priceDay'];
                $now = time();
                //生成POI
                $poiData = [];
                $poiData['typeid'] = 5;//类型为酒店
                $poiData['name'] = $value['name'];//酒店名
                $poiData['eng_name'] = $value['name'];//酒店英文名
                $poiData['remark'] = $value['remarks'];//备注
                $poiData['address'] = $value['address'];//地址
                $poiData['eng_address'] = $value['address'];//英文地址
                $poiData['grade'] = $rank;//星级
                $poiData['create_time'] = $now;
                $poiData['update_time'] = $now;
                $poiData['source'] = "美景";
                $poiData['import_flag'] = 1;
                $cityArr = ['Banff','Canmore','Lake Louise','Calgary','Jasper'];
                //63卡尔加里  121埃德蒙顿
                $poiData['groupid'] = in_array($cityName,$cityArr) ? 63 : 121;
                $oldData = db("poi")->where("eng_name",$value['name'])->field("id")->find();
                if(!empty($oldData)){
                    //更新poi
                    db("poi")->where("eng_name",$value['name'])->update($poiData);
                    //更新hotel_details
                    $detailsData = [];
                    $detailsData['area'] = $cityName;
                    $detailsData['name'] = $value['name'];
                    $detailsData['eng_name'] = $value['name'];
                    $detailsData['rank'] = $rank;
                    $detailsData['address'] = $value['address'];
                    $detailsData['eng_address'] = $value['address'];
                    $detailsData['remarks'] = $value['remarks'];
                    $detailsData['service_fee'] = $value['service_fee'];
                    $detailsData['charging_fee'] = $value['charging_fee'];
                    $detailsData['breakfast_fee'] = $value['breakfast_fee'];

                    $detailsData['remarks'] = str_replace("$","C$",$detailsData['remarks']);
                    $detailsData['service_fee'] = str_replace("$","C$",$detailsData['service_fee']);
                    $detailsData['charging_fee'] = str_replace("$","C$",$detailsData['charging_fee']);
                    $detailsData['breakfast_fee'] = str_replace("$","C$",$detailsData['breakfast_fee']);

                    foreach ($priceData as $k=>$v) {
                        $priceData[$k]['day'] = str_replace("$","C$",$v['day']);
                        $priceData[$k]['price'] = str_replace("$","C$",$v['price']);
                    }

                    $detailsData['price_json'] = json_encode($priceData);
                    db("hotel_details")->where("pid",$oldData['id'])->update($detailsData);

                    echo "！！！！！【".$value['name']."】已存在，更新完成！！！！！<br>";
                    continue;
                }

                db("poi")->insert($poiData);
                $pid = db('poi')->getLastInsID();

                //生成hotel_details
                $detailsData = [];
                $detailsData['pid'] = $pid;//POI  id
                $detailsData['area'] = $cityName;
                $detailsData['name'] = $value['name'];
                $detailsData['eng_name'] = $value['name'];
                $detailsData['rank'] = $rank;
                $detailsData['address'] = $value['address'];
                $detailsData['eng_address'] = $value['address'];
                $detailsData['remarks'] = $value['remarks'];
                $detailsData['service_fee'] = $value['service_fee'];
                $detailsData['charging_fee'] = $value['charging_fee'];
                $detailsData['breakfast_fee'] = $value['breakfast_fee'];

                $detailsData['remarks'] = str_replace("$","C$",$detailsData['remarks']);
                $detailsData['service_fee'] = str_replace("$","C$",$detailsData['service_fee']);
                $detailsData['charging_fee'] = str_replace("$","C$",$detailsData['charging_fee']);
                $detailsData['breakfast_fee'] = str_replace("$","C$",$detailsData['breakfast_fee']);

                foreach ($priceData as $k=>$v) {
                    $priceData[$k]['day'] = str_replace("$","C$",$v['day']);
                    $priceData[$k]['price'] = str_replace("$","C$",$v['price']);
                }

                $detailsData['price_json'] = json_encode($priceData);
                db("hotel_details")->insert($detailsData);

                echo "【".$value['name']."】已插入数据库<br>";
            }
        }

        echo "**********插入基础数据完毕*********";

        die;
    }



}



